package com.navi.loaddata.loadUnit;

import java.net.InetAddress;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;
import java.util.UUID;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.scheduling.annotation.Scheduled;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.navi.loaddata.core.entity.CtProductLocationHis;
import com.navi.loaddata.core.entity.MaxTime;
import com.navi.loaddata.core.gpEntity.MOpeD;
import com.navi.loaddata.core.gpEntity.MProdD;
import com.navi.loaddata.core.gpEntity.MShopD;
import com.navi.loaddata.core.gpEntity.WppAunitHis;
import com.navi.loaddata.core.gpEntity.WppCunitHis;
import com.navi.loaddata.core.gpEntity.WppFunitHis;
import com.navi.loaddata.core.repository.impl.GreenPlumRepositoryImpl;
import com.navi.loaddata.utils.JdbcUtil;
import com.navi.loaddata.utils.pgJDBCUtils;

@Service
@Transactional(transactionManager = "secondaryPlatformTransactionManager")
public class LoadProductLocationHis {

	@Autowired
	private GreenPlumRepositoryImpl dao2;

//	 @Scheduled(cron = "0 0/1 * * * ?")
	public void loadprodHis() {
		try {
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			Timestamp sysTimestamp = null;
			QueryRunner ora = pgJDBCUtils.getQueryRunner_ora();
			List<MaxTime> a = ora.query("select max(mes_timestamp) as time from wpp_aunit_his",
					new BeanListHandler<MaxTime>(MaxTime.class));
			List<MaxTime> c = ora.query("select max(mes_timestamp) as time from wpp_aunit_his",
					new BeanListHandler<MaxTime>(MaxTime.class));
			List<MaxTime> f = ora.query("select max(mes_timestamp) as time from wpp_aunit_his",
					new BeanListHandler<MaxTime>(MaxTime.class));
			List<Long> arrList = new ArrayList<Long>();
			if (a.get(0).getTime() != null && a.size() > 0) {
				arrList.add(a.get(0).getTime().getTime());
			}
			if (c.get(0).getTime() != null && c.size() > 0) {
				arrList.add(c.get(0).getTime().getTime());
			}
			if (f.get(0).getTime() != null && f.size() > 0) {
				arrList.add(f.get(0).getTime().getTime());
			}
			if (arrList.size() > 0) {
				long[] arr = new long[arrList.size()];
				for (int i = 0, j = arrList.size(); i < j; i++) {
					arr[i] = arrList.get(i);
				}
				long temp = 0;
				for (int i = 0; i < arr.length - 1; i++) {
					for (int j = 0; j < arr.length - 1 - i; j++) {
						if (arr[j] > arr[j + 1]) {
							temp = arr[j];
							arr[j] = arr[j + 1];
							arr[j + 1] = temp;
						}
					}
				}
				sysTimestamp = Timestamp
						.valueOf(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date(arr[arr.length - 1])));
			}
			QueryRunner qr_ora = JdbcUtil.getQueryRunner_ora();
			String sql = "";
			String s1 = "";
			String s2 = "";
			Calendar calendar = Calendar.getInstance();
			if (sysTimestamp != null) {
				s1 = sysTimestamp.toString().substring(0, 19);
			} else {
				s1 = Timestamp.valueOf("2017-12-01 18:00:27").toString();
			}
			Date addTime = sdf.parse(s1);
			calendar.setTime(addTime);
			calendar.add(Calendar.MINUTE, 10);
			s2 = sdf.format(calendar.getTime());
			long startTime = System.currentTimeMillis();
			long total = 0L;
			while (true) {
				sql = "SELECT\n" 
						+ "  TrackOut.PRODUCTNAME AS glass_id,\n"
						+ "  TrackOut.PROCESSOPERATIONNAME AS ope_id,\n" 
						+ "  TrackOut.SUBFACTORYNAME AS shop_id,\n"
						+ " TrackOut.PRODUCTSPECNAME AS prod_id,\n"
						+ "  TrackOut.LINENAME AS eqpt_id,\n" 
						+ "  TrackOut.SUBUNITNAME AS sub_eqpt_id,\n"
						+ "  TrackOut.UNITNAME AS unit_eqpt_id,\n" 
						+ "  TrackOut.PORTNAME AS eqpt_port_id,\n"
						+ "  TrackIn.LINERECIPENAME AS recipe_id,\n" 
						+ " TrackIn.BCRECIPEID AS ac_recipe_id,\n"
						+ "  TrackIn.EVENTTIME AS in_timestamp,\n" 
						+ "  TrackOut.EVENTTIME AS out_timestamp,\n"
						+ "  TrackOut.UPDATETIME AS MES_TIMESTAMP,\n" 
						+ "  TrackOut.LOTNAME AS lot_id,\n"
						+ "  TrackOut.CARRIERNAME AS carrier_name,\n" 
						+ "  TrackOut.POSITION,\n" 
//						+ "  C.ARRAYGLASSNAME AS tft_glass_id,\n" 
//						+ "  C.CFGLASSNAME as cf_glass_id\n"
						+ "FROM\n"
						+ "  MES_CT_PRODUCTLOCATIONHIST TrackOut\n" 
						/*+ "LEFT JOIN\n" 
						+ "  MES_PRODUCTHISTORY C\n"
						+ "ON\n"
						+ "  TrackOut.PRODUCTNAME=C.PRODUCTNAME\n"
						+ "  and TrackOut.PROCESSOPERATIONNAME = C.OLDPROCESSOPERATIONNAME\n"
						+ "  and TrackOut.LINENAME = C.MACHINENAME\n"
						+ "  and TrackOut.SUBFACTORYNAME=C.SUBFACTORYNAME\n"*/
						+ "INNER JOIN\n"
						+ "  MES_CT_PRODUCTLOCATIONHIST TrackIn\n"
						+ "ON\n"
						+ "  TrackOut.SUBFACTORYNAME = TrackIn.SUBFACTORYNAME\n"// shop_id
						+ "  AND TrackOut.PRODUCTNAME = TrackIn.PRODUCTNAME\n"// glass_id
						+ "  AND TrackOut.PROCESSOPERATIONNAME = TrackIn.PROCESSOPERATIONNAME\n"// ope_id
						+ "  AND TrackOut.LINENAME = TrackIn.LINENAME\n"// eqpt_id
						+ "  AND TrackOut.unitname = TrackIn.unitname\n"// unit_eqpt_id
						+ "  AND TrackOut.LINKKEY = TrackIn.LINKKEY\n"
						+ "WHERE\n" 
						+ "  TrackOut.EVENTNAME='ProductOut'\n" 
						+ "  AND TrackIn.EVENTNAME='ProductIn'\n"
						+ "  AND TrackOut.EVENTTIME >= to_date('" + s1.substring(0, 19) + "','yyyy/mm/dd HH24:MI:SS')\n"
						+ "  AND TrackOut.EVENTTIME < to_date('" + s2.substring(0, 19) + "','yyyy/mm/dd HH24:MI:SS')";

				List<CtProductLocationHis> cTproductHisList = qr_ora.query(sql,
						new BeanListHandler<CtProductLocationHis>(CtProductLocationHis.class));
				System.out.println("查询出数量：" + cTproductHisList.size());

				if (cTproductHisList != null && cTproductHisList.size() > 0) {
					total = 1L;
					cTproductHisList.forEach(cTproductHis -> {

						// if (cTproductHis.getProd_id().contains("DUMMY")){
						//
						// }
						if ("A".equalsIgnoreCase(cTproductHis.getShop_id().substring(0, 1))) {
//							saveAUnit(cTproductHis);
						} else if ("O".equalsIgnoreCase(cTproductHis.getShop_id().substring(0, 1))) {
//							saveCUnit(cTproductHis);
						} else if ("C".equalsIgnoreCase(cTproductHis.getShop_id().substring(0, 1))) {
//							saveFUnit(cTproductHis);
						}
					});
				}
				;
				Date date = sdf.parse(s2);
				String s = sdf.format(date);
				System.out.println(s);
				long endTime = System.currentTimeMillis();
				long minute = (endTime - startTime) / 60 / 1000;
				System.out.println("执行了：" + minute + "分钟");
				if ((endTime - date.getTime()) <= (10 * 60 * 1000) || (endTime - startTime) >= (5 * 60 * 1000)) {
					if ((endTime - date.getTime()) <= (10 * 60 * 1000) || total > 0) {
						System.out.println("结束：");
						break;
					}
				}
				s1 = s2;
				Date newTime = sdf.parse(s1);
				calendar.setTime(newTime);
				calendar.add(Calendar.MINUTE, 10);
				s2 = sdf.format(calendar.getTime());
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	private void saveFUnit(CtProductLocationHis cTproductHis) {

		String prod_id = cTproductHis.getProd_id();
		String ope_id = cTproductHis.getOpe_id();
		String shop_id = cTproductHis.getShop_id();
		String sub_eqpt_id = cTproductHis.getSub_eqpt_id();
		String glass_id = cTproductHis.getGlass_id();

		if (ope_id == null || ope_id.length() <= 0) {
			return;
		}
		List<MProdD> mProdDS = dao2.list(MProdD.class, "from MProdD where prodId = ? and shop_id = ?", prod_id,shop_id);
		int prodKey = 0;
		if (mProdDS != null && mProdDS.size() > 0) {
			MProdD mProdD = mProdDS.get(0);
			prodKey = mProdD.getProdKey();
		} else {
			prod_id = "---";
		}
		List<MOpeD> mOpeDS = dao2.list(MOpeD.class, "from MOpeD where opeId = ?", ope_id);
		int opeKey = 0;
		if (mOpeDS != null && mOpeDS.size() > 0) {
			MOpeD mOpeD = mOpeDS.get(0);
			opeKey = mOpeD.getOpeKey();
		}
		List<MShopD> mShopDS = dao2.list(MShopD.class, "from MShopD where shopId = ?", shop_id);
		int shopKey = 0;
		if (mShopDS != null && mShopDS.size() > 0) {
			MShopD mShopD = mShopDS.get(0);
			shopKey = mShopD.getShopKey();
		}
		try {
			WppFunitHis wppFUnit = new WppFunitHis();
			// base_id
			if (glass_id.length() == 10) {
				wppFUnit.setBaseId("G");
			} else if (glass_id.length() == 12) {
				wppFUnit.setBaseId("P");
			} else {
				return;
			}

			String unq_seq_id = UUID.randomUUID().toString().replaceAll("-", "").substring(0, 16);
			// if (prodKey != 0) {
			// wppFUnit.setProdKey(prodKey);
			// }
			// if (opeKey != 0) {
			// wppFUnit.setOpeKey(opeKey);
			// }
			// if (opeKey != 0) {
			// wppFUnit.setShopKeyFk(shopKey);
			// }
			if (sub_eqpt_id == null || sub_eqpt_id == "") {
				wppFUnit.setSubEqptId("no");
			} else {
				wppFUnit.setSubEqptId(sub_eqpt_id);
			}
			wppFUnit.setProdKey(prodKey);
			wppFUnit.setOpeKey(opeKey);
			wppFUnit.setShopKeyFk(shopKey);
			wppFUnit.setUnqSeqId(unq_seq_id);
			wppFUnit.setEtlTimestamp(Timestamp.valueOf(LocalDateTime.now()));
			wppFUnit.setEtlEventNode(InetAddress.getLocalHost().getHostAddress());
			wppFUnit.setEtlEventName(InetAddress.getLocalHost().getHostName());
			// etl_evt_seq_id
			wppFUnit.setEtlEvtSeqId(unq_seq_id);
			wppFUnit.setGlassId(glass_id);
			wppFUnit.setOpeId(ope_id);
			wppFUnit.setShopId(shop_id);
			wppFUnit.setProdId(prod_id);
			wppFUnit.setEqptId(cTproductHis.getEqpt_id());
			wppFUnit.setUnitEqptId(cTproductHis.getUnit_eqpt_id());
			wppFUnit.setEqptPortId(cTproductHis.getEqpt_port_id());
			wppFUnit.setRecipeId(cTproductHis.getRecipe_id());
			wppFUnit.setAcRecipeId(cTproductHis.getAc_recipe_id());
			// evt_cate
			wppFUnit.setEvtCate("etl");
			wppFUnit.setEvtUsr("etl");
			wppFUnit.setInTimestamp(cTproductHis.getIn_timestamp());
			wppFUnit.setOutTimestamp(cTproductHis.getOut_timestamp());
			wppFUnit.setEvtTimestamp(cTproductHis.getOut_timestamp());
			// evt_note

			wppFUnit.setMesTimestamp(cTproductHis.getMes_timestamp());
			wppFUnit.setLotId(cTproductHis.getLot_id());
			wppFUnit.setCarrierName(cTproductHis.getCarrier_name());
			wppFUnit.setPosition(cTproductHis.getPosition());
			dao2.update(wppFUnit);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	private void saveCUnit(CtProductLocationHis cTproductHis) {
		String prod_id = cTproductHis.getProd_id();
		String ope_id = cTproductHis.getOpe_id();
		String shop_id = cTproductHis.getShop_id();
		String sub_eqpt_id = cTproductHis.getSub_eqpt_id();
		String glass_id = cTproductHis.getGlass_id();

		if (ope_id == null || ope_id.length() <= 0) {
			return;
		}
		List<MProdD> mProdDS = dao2.list(MProdD.class, "from MProdD where prodId = ?", prod_id);
		int prodKey = 0;
		if (mProdDS != null && mProdDS.size() > 0) {
			MProdD mProdD = mProdDS.get(0);
			prodKey = mProdD.getProdKey();
		} else {
			prod_id = "no";
		}
		List<MOpeD> mOpeDS = dao2.list(MOpeD.class, "from MOpeD where opeId = ?", ope_id);
		int opeKey = 0;
		if (mOpeDS != null && mOpeDS.size() > 0) {
			MOpeD mOpeD = mOpeDS.get(0);
			opeKey = mOpeD.getOpeKey();
		}
		List<MShopD> mShopDS = dao2.list(MShopD.class, "from MShopD where shopId = ?", shop_id);
		int shopKey = 0;
		if (mShopDS != null && mShopDS.size() > 0) {
			MShopD mShopD = mShopDS.get(0);
			shopKey = mShopD.getShopKey();
		}
		try {
			WppCunitHis wppCUnit = new WppCunitHis();
			// base_id
			if (glass_id.length() == 10) {
				wppCUnit.setBaseId("G");
			} else if (glass_id.length() == 12) {
				wppCUnit.setBaseId("P");
			} else {
				return;
			}

			String unq_seq_id = UUID.randomUUID().toString().replaceAll("-", "").substring(0, 16);
			// if (prodKey != 0) {
			// wppCUnit.setProdKey(prodKey);
			// }
			// if (opeKey != 0) {
			// wppCUnit.setOpeKey(opeKey);
			// }
			// if (opeKey != 0) {
			// wppCUnit.setShopKeyFk(shopKey);
			// }
			if (sub_eqpt_id == null || sub_eqpt_id == "") {
				wppCUnit.setSubEqptId("---");
			} else {
				wppCUnit.setSubEqptId(sub_eqpt_id);
			}

			wppCUnit.setProdKey(prodKey);
			wppCUnit.setOpeKey(opeKey);
			wppCUnit.setShopKeyFk(shopKey);
			wppCUnit.setUnqSeqId(unq_seq_id);
			wppCUnit.setEtlTimestamp(Timestamp.valueOf(LocalDateTime.now()));
			wppCUnit.setEtlEventNode(InetAddress.getLocalHost().getHostAddress());
			wppCUnit.setEtlEventName(InetAddress.getLocalHost().getHostName());
			// etl_evt_seq_id
			wppCUnit.setEtlEvtSeqId(unq_seq_id);
			wppCUnit.setGlassId(glass_id);
			wppCUnit.setOpeId(ope_id);
			wppCUnit.setShopId(shop_id);
			wppCUnit.setProdId(prod_id);
			wppCUnit.setEqptId(cTproductHis.getEqpt_id());
			wppCUnit.setUnitEqptId(cTproductHis.getUnit_eqpt_id());
			wppCUnit.setEqptPortId(cTproductHis.getEqpt_port_id());
			wppCUnit.setRecipeId(cTproductHis.getRecipe_id());
			wppCUnit.setAcRecipeId(cTproductHis.getAc_recipe_id());
			// evt_cate
			wppCUnit.setEvtCate("etl");
			wppCUnit.setEvtUsr("etl");
			wppCUnit.setInTimestamp(cTproductHis.getIn_timestamp());
			wppCUnit.setOutTimestamp(cTproductHis.getOut_timestamp());
			wppCUnit.setEvtTimestamp(cTproductHis.getOut_timestamp());
			// evt_note

			wppCUnit.setMesTimestamp(cTproductHis.getMes_timestamp());
			wppCUnit.setLotId(cTproductHis.getLot_id());
			wppCUnit.setCarrierName(cTproductHis.getCarrier_name());
			wppCUnit.setPosition(cTproductHis.getPosition());
			wppCUnit.setTftGlassId(cTproductHis.getTft_glass_id());
			wppCUnit.setCfGlassId(cTproductHis.getCf_glass_id());
			dao2.update(wppCUnit);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	private void saveAUnit(CtProductLocationHis cTproductHis) {
		String prod_id = cTproductHis.getProd_id();
		String ope_id = cTproductHis.getOpe_id();
		String shop_id = cTproductHis.getShop_id();
		String sub_eqpt_id = cTproductHis.getSub_eqpt_id();
		String glass_id = cTproductHis.getGlass_id();

		if (ope_id == null || ope_id.length() <= 0) {
			return;
		}
		List<MProdD> mProdDS = dao2.list(MProdD.class, "from MProdD where prodId = ?", prod_id);
		int prodKey = 0;
		if (mProdDS != null && mProdDS.size() > 0) {
			MProdD mProdD = mProdDS.get(0);
			prodKey = mProdD.getProdKey();
		} else {
			prod_id = "no";
		}
		List<MOpeD> mOpeDS = dao2.list(MOpeD.class, "from MOpeD where opeId = ?", ope_id);
		int opeKey = 0;
		if (mOpeDS != null && mOpeDS.size() > 0) {
			MOpeD mOpeD = mOpeDS.get(0);
			opeKey = mOpeD.getOpeKey();
		}
		List<MShopD> mShopDS = dao2.list(MShopD.class, "from MShopD where shopId = ?", shop_id);
		int shopKey = 0;
		if (mShopDS != null && mShopDS.size() > 0) {
			MShopD mShopD = mShopDS.get(0);
			shopKey = mShopD.getShopKey();
		}
		try {
			WppAunitHis wppAUnit = new WppAunitHis();
			// base_id
			if (glass_id.length() == 10) {
				wppAUnit.setBaseId("G");
			} else if (glass_id.length() == 12) {
				wppAUnit.setBaseId("P");
			} else {
				return;
			}

			String unq_seq_id = UUID.randomUUID().toString().replaceAll("-", "").substring(0, 16);
			// if (prodKey != 0) {
			// wppAUnit.setProdKey(prodKey);
			// }
			// if (opeKey != 0) {
			// wppAUnit.setOpeKey(opeKey);
			// }
			// if (opeKey != 0) {
			// wppAUnit.setShopKeyFk(shopKey);
			// }
			if (sub_eqpt_id == null || sub_eqpt_id == "") {
				wppAUnit.setSubEqptId("no");
			} else {
				wppAUnit.setSubEqptId(sub_eqpt_id);
			}

			wppAUnit.setProdKey(prodKey);
			wppAUnit.setShopKeyFk(shopKey);
			wppAUnit.setOpeKey(opeKey);

			wppAUnit.setUnqSeqId(unq_seq_id);
			wppAUnit.setEtlTimestamp(Timestamp.valueOf(LocalDateTime.now()));
			wppAUnit.setEtlEventNode(InetAddress.getLocalHost().getHostAddress());
			wppAUnit.setEtlEventName(InetAddress.getLocalHost().getHostName());
			// etl_evt_seq_id
			wppAUnit.setEtlEvtSeqId(unq_seq_id);
			wppAUnit.setGlassId(glass_id);
			wppAUnit.setOpeId(ope_id);
			wppAUnit.setShopId(shop_id);
			wppAUnit.setProdId(prod_id);
			wppAUnit.setEqptId(cTproductHis.getEqpt_id());
			wppAUnit.setUnitEqptId(cTproductHis.getUnit_eqpt_id());
			wppAUnit.setEqptPortId(cTproductHis.getEqpt_port_id());
			wppAUnit.setRecipeId(cTproductHis.getRecipe_id());
			wppAUnit.setAcRecipeId(cTproductHis.getAc_recipe_id());
			// evt_cate
			wppAUnit.setEvtCate("etl");
			wppAUnit.setEvtUsr("etl");
			wppAUnit.setInTimestamp(cTproductHis.getIn_timestamp());
			wppAUnit.setOutTimestamp(cTproductHis.getOut_timestamp());
			wppAUnit.setEvtTimestamp(cTproductHis.getOut_timestamp());
			// evt_note

			wppAUnit.setMesTimestamp(cTproductHis.getMes_timestamp());
			wppAUnit.setLotId(cTproductHis.getLot_id());
			wppAUnit.setCarrierName(cTproductHis.getCarrier_name());
			wppAUnit.setPosition(cTproductHis.getPosition());
			// System.out.println(wppAUnit);
			dao2.update(wppAUnit);
		} catch (Exception e) {
			e.printStackTrace();
		}

	}

}
